package com.lute.utils;

import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import org.hibernate.ConnectionReleaseMode;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.oauth2.common.json.JSONException;
import org.springframework.security.oauth2.common.json.JSONObject;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.lute.model.Admin;
import com.lute.model.Approver;
import com.lute.model.BusinessUnit;
import com.lute.model.Client;
import com.lute.model.ClientAccountingPeriod;
import com.lute.model.ClientAdmin;
import com.lute.model.ClientAdminProfile;
import com.lute.model.ClientProfile;
import com.lute.model.CompanyCategory;
import com.lute.model.Employee;
import com.lute.model.EmployeeAccountingPeriod;
import com.lute.model.EmployeeAccountingPeriodState;
import com.lute.model.EmployeeEntry;
import com.lute.model.EmployeeProfile;
import com.lute.model.NotepadEntry;
import com.lute.model.Position;
import com.lute.model.Role;
import com.lute.model.User;

@Repository
public class LuteDAO  implements LuteInterfaceDAO {
	
	@Autowired
	private SessionFactory sessionFactory;
	
	public LuteDAO() {}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=false)
    public void addObjectToDataBase(Object obj) {
		sessionFactory.getCurrentSession().save(obj);
    }
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=false)
    public int addNewEmployeeAndGetIdFromDataBase(Employee obj) {
		sessionFactory.getCurrentSession().save(obj);
		sessionFactory.getCurrentSession().flush();
		return obj.getId_user();
    }
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=false)
	public void updateObjectinDataBase(Object obj) {
	   	sessionFactory.getCurrentSession().update(obj);
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=false)
	public void mergeObjectinDataBase(Object obj) {
		sessionFactory.getCurrentSession().merge(obj);
    }
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public User getUserFromDataBase(String propertyName, String propertyValue) {
		User user = (User)sessionFactory.getCurrentSession().createCriteria(User.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return user;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<User> getEmployeesAndApproversFromDataBase(String propertyName, String propertyValue) {
		List<User> users;
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(User.class);
		Criterion crit1 = Restrictions.eq("user_type", Employee.class);
		Criterion crit2 = Restrictions.eq("user_type", Approver.class);
		Criterion crit3 = Restrictions.eq(propertyName , propertyValue);
		Disjunction disjunction = Restrictions.disjunction();
		disjunction.add(crit1);
		disjunction.add(crit2);
		disjunction.add(crit3);
		criteria.add(disjunction);
		users = criteria.list();
		return users;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public User getUserFromDataBaseByUserId(String propertyName, int propertyValue) {
		User user = (User)sessionFactory.getCurrentSession().createCriteria(User.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return user;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<User> getUsersFromDataBase(){
		List<User> users = sessionFactory.getCurrentSession().createQuery("from User").list();
		return users;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<User> getUsersByUserRoleFromDataBase(String propertyName, int propertyValue){
		List<User> users = sessionFactory.getCurrentSession().createCriteria(User.class).
				add(Restrictions.eq(propertyName, propertyValue)).list();
		return users;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<User> getUsersFromDataBase(String propertyName, String propertyValue) {
		List<User> users = sessionFactory.getCurrentSession().createCriteria(User.class).
				add(Restrictions.eq(propertyName, propertyValue)).list();
		return users;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<User> getUsersFromDataBase(String propertyName1,String propertyValue1,
										   String propertyName2,String propertyValue2) {
		
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(User.class);
		Criterion criterion1 = Restrictions.eq(propertyName1, propertyValue1);
		Criterion criterion2 = Restrictions.eq(propertyName2, propertyValue2);
		Conjunction conjunction = Restrictions.conjunction();
		conjunction.add(criterion1);
		conjunction.add(criterion2);
		criteria.add(conjunction);
		List<User> users = criteria.list(); 
		return users;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Admin> getAdminsFromDataBase(String propertyName, int propertyValue) {
		List<Admin> admins = sessionFactory.getCurrentSession().createCriteria(Admin.class).
				add(Restrictions.eq(propertyName, propertyValue)).list();
		return admins;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBase(String propertyName, String propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list = employee.getEmployeeAccountingPeriods();
		list.size();
		return employee;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBaseById(String propertyName, int propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return employee;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBaseByIdWithProfile(String propertyName, int propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		EmployeeProfile profile = employee.getEmployeeProfil();
		profile.toString();
		return employee;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBaseByIdWithAccPeriod(String propertyName, int propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list = employee.getEmployeeAccountingPeriods();
		list.size();
		return employee;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBaseByIdWithAccPeriodNotepadEntries(String propertyName, int propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> eapList = employee.getEmployeeAccountingPeriods();
		eapList.size();
		
		for (EmployeeAccountingPeriod employeeAccountingPeriod : eapList) {
			List<NotepadEntry> neList = employeeAccountingPeriod.getNotepadEntries();
			neList.size();
		}
		return employee;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Employee> getEmployeesFromDataBase(){
		List<Employee> employee = sessionFactory.getCurrentSession().createCriteria(Employee.class).list();
		return employee;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Employee> getEmployeesByAccLockedFromDataBase(String propertyName, String propertyValue) {
		List<Employee> employee = sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).list();
		return employee;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Employee getEmployeeFromDataBaseByIdWithAccPeriodClientAccPeriodEmployeeEntries(String propertyName, int propertyValue){
		Employee employee = (Employee)sessionFactory.getCurrentSession().createCriteria(Employee.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> eapList = employee.getEmployeeAccountingPeriods();
		eapList.size();
		
		for (EmployeeAccountingPeriod employeeAccountingPeriod : eapList) {
			ClientAccountingPeriod cap = employeeAccountingPeriod.getClientAccountingPeriod();
			cap.toString();
			List<EmployeeEntry> eeList = employeeAccountingPeriod.getEmployeeEntries();
			eeList.size();
		}
		return employee;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public EmployeeProfile getEmployeeProfileByEmployeeIdFromDataBase(String role, int userId) {
		List<EmployeeProfile> appProfiles = null;
		EmployeeProfile employeeProfile = null;
		
		if(role.equals("employee")) {
			employeeProfile = (EmployeeProfile)sessionFactory.getCurrentSession().createCriteria(EmployeeProfile.class)
					.add(Restrictions.eq(role+"."+"id_user", userId)).uniqueResult();
		} else if(role.equals("approver")) {
			appProfiles = (List<EmployeeProfile>)sessionFactory.getCurrentSession().createCriteria(EmployeeProfile.class)
					.add(Restrictions.eq(role+"."+"id_user", userId)).list();
			for(EmployeeProfile profile: appProfiles) {
				if((profile.getEmployee() == null) && (profile.getApprover().getId_user() == userId)) {
					System.out.println("ProfileId"+profile.getId_employee_profile());
					employeeProfile = profile;
					return employeeProfile;
				}
			}
		} else {
			/* for future cases*/
		}
		return employeeProfile;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
    public List<EmployeeProfile> getEmployeeProfilesFromDataBase() {
		Query query = sessionFactory.getCurrentSession().createQuery("from EmployeeProfile");
		List<EmployeeProfile> employeeProfiles = query.list(); 
		return employeeProfiles;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
    public List<EmployeeProfile> getEmployeeProfilesByUserIdsFromDataBase(List<Integer> employeeIds) {
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(EmployeeProfile.class);
		Disjunction disjunction = Restrictions.disjunction();
		for(Integer obj: employeeIds) {
			Criterion criterion1 = Restrictions.eq("approver.id", obj);
			Criterion criterion2 = Restrictions.eq("employee.id", obj);
			disjunction.add(criterion1);
			disjunction.add(criterion2);
		}
		criteria.add(disjunction);
		return criteria.list();
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBase(String propertyName, String propertyValue) {
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<Employee> list = approver.getEmployees();
		list.size();
		return approver;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Approver> getApproversFromDataBase(){
		List<Approver> approvers = sessionFactory.getCurrentSession().createCriteria(Approver.class).list();
		return approvers;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Approver> getApproversByAccLockedFromDataBase(String propertyName, String propertyValue) {
		List<Approver> approvers = sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).list();
		return approvers;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBaseById(String propertyName, int propertyValue){
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return approver;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBaseByIdWithAccPeriod(String propertyName, int propertyValue){
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list = approver.getApproverAccountingPeriods();
		list.size();
		return approver;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBaseByIdWithEmployees(String propertyName, int propertyValue){
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<Employee> list = approver.getEmployees();
		list.size();
		return approver;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBaseByIdWithAll(String propertyName, int propertyValue){
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list1 = approver.getApproverAccountingPeriods();
		list1.size();
		List<Employee> list2 = approver.getEmployees();
		list2.size();
		EmployeeProfile profile = approver.getApproverProfile();
		profile.toString();
		return approver;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Approver getApproverFromDataBaseByIdWithAllApproverEntries(String propertyName, int propertyValue){
		Approver approver = (Approver)sessionFactory.getCurrentSession().createCriteria(Approver.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list1 = approver.getApproverAccountingPeriods();
		list1.size();
		List<Employee> list2 = approver.getEmployees();
		list2.size();
		EmployeeProfile profile = approver.getApproverProfile();
		profile.toString();
		
		for (EmployeeAccountingPeriod employeeAccountingPeriod : list1) {
			ClientAccountingPeriod cap = employeeAccountingPeriod.getClientAccountingPeriod();
			cap.getClient().getName();
			List<EmployeeEntry> eeList = employeeAccountingPeriod.getEmployeeEntries();
			eeList.size();
		}

		return approver;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAdmin getClientAdminFromDataBaseById(String propertyName, int propertyValue) {
		ClientAdmin clientAdmin = (ClientAdmin)sessionFactory.getCurrentSession().createCriteria(ClientAdmin.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		ClientAdminProfile profile = clientAdmin.getClientAdminProfil();
		List<Client> list = profile.getClients();
		list.size();
		return clientAdmin;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAdmin getClientAdminFromDataBaseByIdWithClients(String propertyName, int propertyValue) {
		ClientAdmin clientAdmin = (ClientAdmin)sessionFactory.getCurrentSession().createCriteria(ClientAdmin.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		ClientAdminProfile profile = clientAdmin.getClientAdminProfil();
		List<Client> list = profile.getClients();
		list.size();
		return clientAdmin;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<ClientAdmin> getClientAdminFromDataBaseByAccLocked(String propertyName, String propertyValue) {
		List<ClientAdmin> clientAdmins = sessionFactory.getCurrentSession().createCriteria(ClientAdmin.class)
				.add(Restrictions.eq(propertyName, propertyValue)).list();
		return clientAdmins;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAdmin getClientAdminFromDataBaseByIdWithClientsClientAccPeriodsEmployeeAccPeriodsEmployees(String propertyName, int propertyValue) {
		ClientAdmin clientAdmin = (ClientAdmin)sessionFactory.getCurrentSession().createCriteria(ClientAdmin.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		
		List<Client> clientList = clientAdmin.getClientAdminProfil().getClients();
		clientList.size();
		
		for (Client client : clientList) {
			List<ClientAccountingPeriod> clientAccPeriodList = client.getClientAccountingPeriods();
			clientAccPeriodList.size();
			
			for (ClientAccountingPeriod clientAccountingPeriod : clientAccPeriodList) {
				List<EmployeeAccountingPeriod> employeeAccPeriodList = clientAccountingPeriod.getEmployeeAccountingPeriod();
				employeeAccPeriodList.size();
				
				for (EmployeeAccountingPeriod employeeAccountingPeriod : employeeAccPeriodList) {
					if(employeeAccountingPeriod.getEmployee() == null) {
						Approver approver = employeeAccountingPeriod.getApprover();
						approver.toString();
					} 
					
					if (employeeAccountingPeriod.getApprover() == null){
						Employee employee = employeeAccountingPeriod.getEmployee();
						employee.toString();
					}
				}
			}
		}

		return clientAdmin;
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public EmployeeProfile getEmployeeProfileById(String propertyName, int propertyValue) {
		EmployeeProfile employeeProfile = (EmployeeProfile)sessionFactory.getCurrentSession().createCriteria(EmployeeProfile.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return employeeProfile;
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<EmployeeProfile> getEmployeeProfileByClientIdFromDataBase(String propertyName, int propertyValue) {
		List<EmployeeProfile> employeeProfiles = (List<EmployeeProfile>)sessionFactory.getCurrentSession().createCriteria(EmployeeProfile.class)
				.add(Restrictions.eq(propertyName, propertyValue)).list();
		return employeeProfiles;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Client getClientFromDataBase(String propertyName, String propertyValue){
		Client client = (Client)sessionFactory.getCurrentSession().createCriteria(Client.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return client;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<Client> getClientsFromDataBase(){
		List<Client> clients = sessionFactory.getCurrentSession().createCriteria(Client.class).list();
		return clients;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Client getClientFromDataBaseById(String propertyName, int propertyValue){
		Client client = (Client)sessionFactory.getCurrentSession().createCriteria(Client.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return client;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Client getClientWithAccPeriodByIdFromDataBase(String propertyName, int propertyValue) {
		Client client = (Client)sessionFactory.getCurrentSession().createCriteria(Client.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<ClientAccountingPeriod> clientAccPeriod = client.getClientAccountingPeriods();
		clientAccPeriod.size();
		return client;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Client getClientWithBusinessUnitsByIdFromDataBase(String propertyName, int propertyValue) {
		Client client = (Client)sessionFactory.getCurrentSession().createCriteria(Client.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<BusinessUnit> businessUnits = client.getBusiness_units();
		businessUnits.size();
		return client;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAccountingPeriod getClientAccountingPeriodByIdFromDataBase(String propertyName, int propertyValue) {
		ClientAccountingPeriod clientAccountingPeriod = (ClientAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(ClientAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return clientAccountingPeriod;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAccountingPeriod getClientAccountingPeriodWithEmployeeAccPeriodsByIdFromDataBase(String propertyName, int propertyValue) {
		ClientAccountingPeriod clientAccountingPeriod = (ClientAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(ClientAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> emplPeriods = clientAccountingPeriod.getEmployeeAccountingPeriod();
		emplPeriods.size();
		return clientAccountingPeriod;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAccountingPeriod getClientCurrentAccountingPeriodFromDataBase(String propertyName, int propertyValue) {
		ClientAccountingPeriod clientAccountingPeriod = null;
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(ClientAccountingPeriod.class);
		Conjunction conjunction = Restrictions.conjunction();
		Criterion criteria1 = Restrictions.eq(propertyName, propertyValue);
		Criterion criteria2 = Restrictions.eq("clientAccountingPeriodState", "open");
		conjunction.add(criteria1);
		conjunction.add(criteria2);
		criteria.add(conjunction);
		clientAccountingPeriod = (ClientAccountingPeriod)criteria.uniqueResult();
		return clientAccountingPeriod;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAccountingPeriod getClientAccountingPeriodWithEmployeeAccPeriodsBySpecifiedClientFromDataBase(String propertyName1, int propertyValue,
																											   String propertyName2, GregorianCalendar cal) {
		ClientAccountingPeriod period = null;
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(ClientAccountingPeriod.class);
		Criterion criterion1 = Restrictions.eq(propertyName1, propertyValue);
		Criterion criterion2 = Restrictions.eq(propertyName2, cal);
		Conjunction conjunction = Restrictions.conjunction();
		conjunction.add(criterion1);
		conjunction.add(criterion2);
		criteria.add(conjunction);
		period = (ClientAccountingPeriod)criteria.uniqueResult();
		if(period != null) {
			List<EmployeeAccountingPeriod> emplPeriods = period.getEmployeeAccountingPeriod();
			emplPeriods.size();
		}
		return period;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientProfile getClientProfileById(String propertyName, int propertyValue){
		ClientProfile clientProfile = (ClientProfile)sessionFactory.getCurrentSession().createCriteria(ClientProfile.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return clientProfile;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAdminProfile getClientAdminProfileByClientAdminIdFromDataBase(int propertyValue) {
		ClientAdminProfile clientAdminProfile = (ClientAdminProfile)sessionFactory.getCurrentSession().createCriteria(ClientAdminProfile.class)
				.add(Restrictions.eq("clientAdmin.id", propertyValue)).uniqueResult();
		return clientAdminProfile;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<ClientAdminProfile> getClientAdminProfilesFromDataBase() {
		Query query = sessionFactory.getCurrentSession().createQuery("from ClientAdminProfile");
		List<ClientAdminProfile> clientAdminProfile = query.list();
		return clientAdminProfile;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<ClientAdminProfile> getClientAdminProfilesWithClientsFromDataBase() {
		Query query = sessionFactory.getCurrentSession().createQuery("from ClientAdminProfile");
		List<ClientAdminProfile> clientAdminProfile = query.list();
		for(ClientAdminProfile profile: clientAdminProfile) {
			List<Client> clients = profile.getClients();
			clients.size();
		}
		return clientAdminProfile;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAdminProfile getClientAdminProfileByClientAdminIdWithClientsFromDataBase(int propertyValue) {
		ClientAdminProfile clientAdminProfile = (ClientAdminProfile)sessionFactory.getCurrentSession().createCriteria(ClientAdminProfile.class)
				.add(Restrictions.eq("clientAdmin.id", propertyValue)).uniqueResult();
		List<Client> clients = clientAdminProfile.getClients();
		clients.size();
		return clientAdminProfile;
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientProfile getClientProfileByIdWithClientsAdmin(String propertyName, int propertyValue){
		ClientProfile clientProfile = (ClientProfile)sessionFactory.getCurrentSession().createCriteria(ClientProfile.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<ClientAdmin> clientsAdmin = clientProfile.getClientAdmin();
		clientsAdmin.size();
		return clientProfile;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<ClientProfile> getClientProfileByIdWithClientsAdmin(String propertyName, int[] propertyValues){
		List<ClientProfile> clientProfiles;
		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(ClientProfile.class);
		Disjunction disjunction = Restrictions.disjunction();
		for(int i = 0; i < propertyValues.length; i++) {
			Criterion criterion = Restrictions.eq(propertyName, propertyValues[i]);
			disjunction.add(criterion);
		}
		criteria.add(disjunction);
		clientProfiles = criteria.list();
		if(clientProfiles != null) {
			for(ClientProfile clientProfile: clientProfiles) {
				List<ClientAdmin> clientsAdmin = clientProfile.getClientAdmin();
				clientsAdmin.size();
			}
		} else {
			return null;
		}
		return clientProfiles;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
    public ClientAdminProfile getProfileFromDataBase(String propertyName,String propertyValue) {
		Session session = sessionFactory.openSession();
		ClientAdminProfile prof = (ClientAdminProfile)session.createCriteria(ClientAdminProfile.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<Client> list1 = prof.getClients();
		list1.size();
		return prof;
	}
//	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
//    public List<ClientAdminProfile> getClientAdminProfilesByUserIdsFromDataBase(List<Integer> clientAdminIds) {
//		Criteria criteria = sessionFactory.getCurrentSession().createCriteria(ClientAdminProfile.class);
//		Disjunction disjunction = Restrictions.disjunction();
//		for(Integer obj: clientAdminIds) {
//			Criterion criterion = Restrictions.eq("clientAdmin.id", obj);
//			disjunction.add(criterion);
//		}
//		criteria.add(disjunction);
//		return criteria.list();
//	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public ClientAccountingPeriod getClientAccountingPeriodFromDataBase(String propertyName, String propertyValue) {
		ClientAccountingPeriod clientAccountingPeriod = (ClientAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(ClientAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeAccountingPeriod> list = clientAccountingPeriod.getEmployeeAccountingPeriod();
		list.size();
		return clientAccountingPeriod;	
	}
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public EmployeeAccountingPeriod getEmployeeAccountingPeriodFromDataBase(String propertyName, String propertyValue) {
		EmployeeAccountingPeriod employeeAccountingPeriod = (EmployeeAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(EmployeeAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeEntry> list = employeeAccountingPeriod.getEmployeeEntries();
		list.size();
		return employeeAccountingPeriod;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<EmployeeAccountingPeriod> getEmployeeAccountingPeriodsWithEntriesFromDataBase(List<EmployeeAccountingPeriod> periods) {
		List<EmployeeAccountingPeriod> employeePeriods = new ArrayList<EmployeeAccountingPeriod>();
		for(EmployeeAccountingPeriod period: periods) {
			period = (EmployeeAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(EmployeeAccountingPeriod.class)
					.add(Restrictions.eq("id_employee_accounting_period", period.getId_employee_accounting_period())).uniqueResult();
			List<EmployeeEntry> list = period.getEmployeeEntries();
			list.size();
			employeePeriods.add(period);
		}	
		return employeePeriods;	
	}
	
	@Transactional(propagation=Propagation.REQUIRED,rollbackFor=IllegalStateException.class, readOnly=false)
    public void updateEmployeeAccountingPeriodsInDataBase(List<EmployeeAccountingPeriod> periods) throws IllegalStateException {
		Session session = sessionFactory.getCurrentSession();
		for(EmployeeAccountingPeriod period: periods) {
			if(period.getEmployeeAccountingPeriodState().equals(EmployeeAccountingPeriodState.REJECTED_BY_APPROVER.toString())) { 
				throw new IllegalStateException();
			}
			if(!(period.getEmployeeAccountingPeriodState().equals(EmployeeAccountingPeriodState.CHECKED_BY_CLIENT_ADMIN.toString()) ||
				 period.getEmployeeAccountingPeriodState().equals(EmployeeAccountingPeriodState.REJECTED_BY_CLIENT_ADMIN.toString()))) {
				
					period.setEmployeeAccountingPeriodState(EmployeeAccountingPeriodState.SENT_TO_CLIENT_ADMIN.toString());
			}
			session.merge(period);
		}
    }
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<EmployeeAccountingPeriod> getEmployeeAccountingPeriodsFromDataBase(List<Integer> employeeIds, GregorianCalendar from) {
		List<EmployeeAccountingPeriod> employeeAccountingPeriod = new ArrayList<EmployeeAccountingPeriod>();
		String hql = "from EmployeeAccountingPeriod where calFrom = :from"+
				 " AND ("+ "employee.id = :employeeId" + " OR " + "approver.id = :employeeId )";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		System.out.println("Time"+from.getTime());
		for(int i = 0; i < employeeIds.size(); i++) {
			query.setParameter("from", from);
			query.setParameter("employeeId", employeeIds.get(i));
			EmployeeAccountingPeriod period = (EmployeeAccountingPeriod)query.uniqueResult();
			System.out.println(employeeIds.get(i));
			List<EmployeeEntry> entries = period.getEmployeeEntries();
			entries.size();
			if(period != null)employeeAccountingPeriod.add(period);
		}
		return employeeAccountingPeriod;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public List<EmployeeAccountingPeriod> getEmployeeAccountingPeriodsByClientIdFromDataBase(String propertyName, int propertyValue) {
		List<EmployeeAccountingPeriod> employeeAccountingPeriods = (List<EmployeeAccountingPeriod>)sessionFactory.getCurrentSession().createCriteria(EmployeeAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).list();
		return employeeAccountingPeriods;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public EmployeeAccountingPeriod getEmployeeAccountingPeriodFromDataBaseById(String propertyName, int propertyValue) {
		EmployeeAccountingPeriod employeeAccountingPeriod = (EmployeeAccountingPeriod)sessionFactory.getCurrentSession().createCriteria(EmployeeAccountingPeriod.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		List<EmployeeEntry> list = employeeAccountingPeriod.getEmployeeEntries();
		list.size();
		return employeeAccountingPeriod;
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Role getRoleFromDataBase(String propertyName, String propertyValue) {
		Role role = (Role)sessionFactory.getCurrentSession().createCriteria(Role.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return role;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	   public void changeObjectType(Approver object, int id, Class<Employee> genericType, String idColumn,
	            String discriminatorColumn, String discriminatorValue) {
		   sessionFactory.getCurrentSession().evict(object);
	        Query query = sessionFactory.getCurrentSession().createQuery(
	            "update " + genericType.getName() + " " +
	            "set " + discriminatorColumn + " = :newType " +
	            "where " + idColumn + " = :id)");
	        query.setLong("id", id);
	        query.setString("newType", discriminatorValue);
	        query.executeUpdate();
	        sessionFactory.getCurrentSession().flush();
	        //TODO check if exist way to swap objects, not only "type"
	   }
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public void deleteNotepadEntriesFromEmployeeAccountingPeriod(List<NotepadEntry> notepadEntriesToDelete){
		if (notepadEntriesToDelete.size() == 0)
			return;
		for (NotepadEntry notepadEntry : notepadEntriesToDelete) {
			NotepadEntry ne = (NotepadEntry) sessionFactory.getCurrentSession().load(NotepadEntry.class, new Integer(notepadEntry.getId_notepad_entry()));
			sessionFactory.getCurrentSession().delete(ne);
		}
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public void deleteEmployeeEntriesFromEmployeeAccountingPeriod(List<EmployeeEntry> employeeEntriesToDelete){
		if (employeeEntriesToDelete.size() == 0)
			return;
		for (EmployeeEntry employeeEntry : employeeEntriesToDelete) {
			EmployeeEntry ee = (EmployeeEntry) sessionFactory.getCurrentSession().load(EmployeeEntry.class, new Integer(employeeEntry.getId_employee_entry()));
			System.out.println("EmployeeEntryId = " + ee.getId_employee_entry() + " deleted");
			sessionFactory.getCurrentSession().delete(ee);
		}
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public CompanyCategory getCompanyCategoryFromDataBase(String propertyName, String propertyValue){
		CompanyCategory cc = (CompanyCategory)sessionFactory.getCurrentSession().createCriteria(CompanyCategory.class)
							.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return cc;
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public BusinessUnit getBusinessUnitFromDataBase(String propertyName, String propertyValue){
		BusinessUnit businessUnit = (BusinessUnit)sessionFactory.getCurrentSession().createCriteria(BusinessUnit.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return businessUnit;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public BusinessUnit getBusinessUnitFromDataBaseById(String propertyName, int propertyValue){
		BusinessUnit businessUnit = (BusinessUnit)sessionFactory.getCurrentSession().createCriteria(BusinessUnit.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return businessUnit;	
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Position getPositionFromDataBase(String propertyName, String propertyValue){
		Position position = (Position)sessionFactory.getCurrentSession().createCriteria(Position.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return position;	
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public Position getPositionFromDataBaseById(String propertyName, int propertyValue){
		Position position = (Position)sessionFactory.getCurrentSession().createCriteria(Position.class)
				.add(Restrictions.eq(propertyName, propertyValue)).uniqueResult();
		return position;	
	}
}
